package com.os.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.os.dao.DeskDaoH;
import com.os.model.DeskH;
import com.os.util.JdbcConUtilM;
/**
 * 点菜单dao实现类
 * @author HQG 2017年1月5日
 *
 */
public class DeskDaoImplH implements DeskDaoH{

	@Override
	public List<DeskH> getAllNoAuditDeskH(int page,int rows) {
		List<DeskH> desks = new ArrayList<DeskH>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from o_desk");
		sql.append(" where fstate=-1");
		sql.append(" limit ?,?");
		Connection con = JdbcConUtilM.getCon();
		try {
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ps.setInt(1, (page-1)*rows);
			ps.setInt(2, rows);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				DeskH d = new DeskH();
				d.setOid(rs.getInt("oid"));
				d.setDid(rs.getInt("did"));
				d.setFoodlist(rs.getString("foodlist"));
				d.setFstate(rs.getInt("fstate"));
				d.setState("未审批!");
				d.setWid(rs.getInt("wid"));
				d.setFnum(rs.getInt("fnum"));
				d.setFtime(rs.getString("ftime"));
				desks.add(d);
			}
			JdbcConUtilM.closePstAndCon(ps, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return desks;
	}

	@Override
	public List<DeskH> getAllDeskH(int page,int rows) {
		List<DeskH> desks = new ArrayList<DeskH>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from o_desk");
		sql.append(" limit ?,?");
		Connection con = JdbcConUtilM.getCon();
		try {
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ps.setInt(1, (page-1)*rows);
			ps.setInt(2, rows);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				DeskH d = new DeskH();
				d.setOid(rs.getInt("oid"));
				d.setDid(rs.getInt("did"));
				d.setFoodlist(rs.getString("foodlist"));
				d.setFstate(rs.getInt("fstate"));
				if(rs.getInt("fstate")==-1){
					d.setState("未审批!");					
				}else if(rs.getInt("fstate")==1){
					d.setState("审批通过!");
				}else{
					d.setState("审批未通过!");
				}
				d.setWid(rs.getInt("wid"));
				d.setFnum(rs.getInt("fnum"));
				d.setFtime(rs.getString("ftime"));
				desks.add(d);
			}
			JdbcConUtilM.closePstAndCon(ps, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return desks;
	}

	@Override
	public StringBuffer getFnamesByFoodlistH(String foodlist) {
		StringBuffer fnames = new StringBuffer();
		String [] fids = foodlist.split(",");
		StringBuffer sql = new StringBuffer();
		sql.append("select fid,fname from o_food");
		sql.append(" where fid in ("+foodlist+")");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement ps;
		try {
			ps = con.prepareStatement(sql.toString());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int num = 0;
				for (int i = 0; i < fids.length; i++) {
					int fid = Integer.parseInt(fids[i]);
					if (rs.getInt("fid")==fid) {
						num++;
					}
				}
				String fname = rs.getString("fname")+":"+num+"份"+"\t"+"\t";
				fnames.append(fname);
			}
			JdbcConUtilM.closePstAndCon(ps, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return fnames;
	}

	@Override
	public String getWnameByWidH(int wid) {
		String wname = new String();
		StringBuffer sql = new StringBuffer();
		sql.append("select uname from o_user");
		sql.append(" where uid=?");
		Connection con = JdbcConUtilM.getCon();
		try {
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ps.setInt(1, wid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				wname = rs.getString("uname");
			}
			JdbcConUtilM.closePstAndCon(ps, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return wname;
	}

	@Override
	public DeskH getDeskByOidH(int oid) {
		DeskH desk = new DeskH();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from o_desk");
		sql.append(" where oid=?");
		Connection con = JdbcConUtilM.getCon();
		try {
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ps.setInt(1, oid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				desk.setOid(rs.getInt("oid"));
				desk.setDid(rs.getInt("did"));
				desk.setFoodlist(rs.getString("foodlist"));
				desk.setFstate(rs.getInt("fstate"));
				if(rs.getInt("fstate")==-1){
					desk.setState("未审批!");					
				}else if(rs.getInt("fstate")==1){
					desk.setState("审批通过!");
				}else{
					desk.setState("审批未通过!");
				}
				desk.setWid(rs.getInt("wid"));
				desk.setFnum(rs.getInt("fnum"));
				desk.setFtime(rs.getString("ftime"));
			}
			JdbcConUtilM.closePstAndCon(ps, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return desk;
	}

	@Override
	public int getAllDesknumH() {
		int count = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("select count(oid) scount");
		sql.append(" from o_desk");
		Connection con = JdbcConUtilM.getCon();
		try {
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				count=rs.getInt("scount");
			}
			JdbcConUtilM.closePstAndCon(ps, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public int getAllNoAuditDesknumH() {
		int count = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("select count(oid) scount");
		sql.append(" from o_desk");
		sql.append(" where fstate=-1");
		Connection con = JdbcConUtilM.getCon();
		try {
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				count=rs.getInt("scount");
			}
			JdbcConUtilM.closePstAndCon(ps, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public int setDeskByFstateH(int fstate, int oid) {
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE o_desk");
		sql.append(" set fstate=?");
		sql.append(" where oid=?");
		Connection con = JdbcConUtilM.getCon();
		try {
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ps.setInt(1, fstate);
			ps.setInt(2, oid);
			flag = ps.executeUpdate();
			JdbcConUtilM.closePstAndCon(ps, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public double getFpriceByFoodlist(String foodlist) {
		double fprice = 0;
		String [] fids = foodlist.split(",");
		Connection con =  JdbcConUtilM.getCon();
		PreparedStatement ps = null;
		for (int i = 0; i < fids.length; i++) {
			StringBuffer sql = new StringBuffer();
			sql.append("select fprice from o_food");
			sql.append(" where fid=?");
			try {
				ps = con.prepareStatement(sql.toString());
				ps.setInt(1, Integer.parseInt(fids[i]));
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {
					fprice += rs.getDouble("fprice");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		JdbcConUtilM.closePstAndCon(ps, con);
		return fprice;
	}
}
